-- STANDARD SCRIPT
-- STEP 1 BELOW HAS TO BE CUSTOMIZED FOR EACH CLIENT 
--


--------------------------------------------------------------------------------------
-- FUNCTION TO LOAD FROM temp_batch_insurance0_t TO batch_insurance0_t DEDUPING BY (hid,acctnum) 


\set ON_ERROR_STOP 1
CREATE OR REPLACE FUNCTION batch_insurance0_deduping_f() RETURNS integer AS $$
DECLARE
  v_status integer;
  v_batch_insurance0_received integer;
  v_batch_run_date timestamp;
  v_cust_id integer;
BEGIN


SELECT INTO v_batch_run_date our_run_date from temp_batch_insurance0_t limit 1;
SELECT INTO v_cust_id cust_id from temp_batch_insurance0_t limit 1;

PERFORM ml_upd_batch_status_insurance0_new_f(v_cust_id,v_batch_run_date,1,2,'batch_insurance0_t LOADING');




-- STEP 1 DETERMINE WHICH ACCOUNT HAS ALREADY BEEN PROCESSED 
--

RAISE NOTICE 'START IDENTIFICATION OF ACCOUNTS IN temp_batch_insurance0_t THAT HAVE BEEN PROCESSED BEFORE';

  UPDATE temp_batch_insurance0_t
  SET
     prior_commercial_run     =  a.prior_commercial_run,
     prior_tpl_run            =  a.prior_tpl_run,
     prior_medicaid_run       =  a.prior_medicaid_run,
     prior_medicare_run       =  a.prior_medicare_run,
     acctnum_seq              =  a.acctnum_seq + 1 
                          
  FROM

   ( SELECT distinct on (cust_id,hid,acctnum) 
            cust_id,
            hid,
            acctnum,
            prior_commercial_run,
            prior_tpl_run,
            prior_medicaid_run,
            prior_medicare_run,
            acctnum_seq 
     FROM
           insurance1_t

     ORDER BY
           cust_id,
           hid,
           acctnum,
           acctnum_seq DESC
   ) as a

  WHERE
    (a.cust_id = temp_batch_insurance0_t.cust_id) AND (a.acctnum = temp_batch_insurance0_t.acctnum ) AND (a.hid = temp_batch_insurance0_t.hid);

RAISE NOTICE 'FINISH IDENTIFICATION OF ACCOUNTS IN temp_batch_insurance0_t THAT HAVE BEEN PROCESSED BEFORE ';




-- STEP 2 - SCRATCH batch_insurance0_t 
-- 

TRUNCATE batch_insurance0_t;




-- STEP 3 - INSERTING INTO batch_insurance0_t ONLY IF NEW ACCOUNT
-- WE ALSO DEDUP


INSERT INTO batch_insurance0_t
SELECT
    b.*
FROM
    (SELECT DISTINCT ON (a.cust_id,a.hid,a.acctnum) a.* 
     
      FROM 
           (SELECT * FROM temp_batch_insurance0_t
            WHERE
           	( (prior_commercial_run IS NULL OR prior_commercial_run  = 'N') 
                  OR
                  (prior_tpl_run IS NULL OR prior_tpl_run  = 'N')
                  OR
                  (prior_medicaid_run IS NULL OR prior_medicaid_run  = 'N')
                  OR
                  (prior_medicare_run IS NULL OR prior_medicare_run  = 'N')
                )                                         AND 
           	service_date_begin IS NOT NULL            AND 
                (charges IS NULL OR charges <> 0)         AND
           	dob                IS NOT NULL            AND 
           	lname              IS NOT NULL            AND 
           	(ssn IS NOT NULL or (fname is not null and state is not null))
            ) as a

      ORDER BY a.cust_id,a.hid,a.acctnum,a.seqnum

    ) as b 
;



GET DIAGNOSTICS v_status = ROW_COUNT;
RAISE NOTICE 'FINISH LOADING batch_insurance0_t TABLE : %', v_status;



SELECT INTO v_batch_insurance0_received count(*) from temp_batch_insurance0_t ;

UPDATE batch_status_insurance0_t
SET
   temp_batch_insurance0_received  = v_batch_insurance0_received,
   batch_insurance0_inserted       = v_status
 WHERE
  (batch_status_insurance0_t.last_run_date = v_batch_run_date and batch_status_insurance0_t.cust_id = v_cust_id);


--PERFORM ml_upd_batch_status_insurance0_new_f(v_cust_id,v_batch_run_date,1,2,'FINISH LOADING OF batch_insurance0_t');


  return v_status;
END;
$$ LANGUAGE plpgsql;
